﻿ui = qt.load_ui("skip_more_than.ui")

function default()
ui.filenameLabel:setText("Файл не выбран!")
--ui.statusLabel:setText("Ожидание данных")
ui.groupBox.weekSpin:setValue(qtt.week_number())
ui.groupBox.monthCombo:setCurrentIndex(qtt.month_number() - 1)
has_file = false
end;

default()
qt.connect(ui.browseButton, "clicked",
	function()
		file = qtt.get_filename("Выберите файл для сохранения отчёта", "*.csv (CSV Files)");
		if file:len() > 0 then
			ui.filenameLabel:setText(file .. ".csv")
			has_file = true
		end;
	end
);

qt.connect(ui.execButton, "clicked",
	function()
		if has_file == false then
			qtt.warning("Ошибка", "Вы должны выбрать файл!")
			return
		end
	
		if ui.groupBox.monthRadio.checked == true then
			cond = "AND MONTH(`date`)='" .. ui.groupBox.monthCombo.currentIndex + 1 .. "'"
		end
		if ui.groupBox.weekRadio.checked == true then
			cond = "AND WEEK(`date`)='" .. ui.groupBox.weekSpin.value .. "'"
		end
		sql.exec([[
			SELECT CONCAT(`people`.`first_name`, ' ', `people`.`last_name`, ' ', `people`.`middle_name`), `groups`.`name`, COUNT(*) FROM `log`
			LEFT JOIN `people` ON `people`.`id`=`log`.`people_id`
			LEFT JOIN `groups` ON `people`.`group`=`groups`.`id`
			WHERE `log`.`type`='skip' ]] .. cond .. [[ 
			GROUP BY `log`.`people_id`
			ORDER BY `groups`.`name`
		]])
		print([[
			SELECT CONCAT(`people`.`first_name`, ' ', `people`.`last_name`, ' ', `people`.`middle_name`), `groups`.`name`, COUNT(*) FROM `log`
			LEFT JOIN `people` ON `people`.`id`=`log`.`people_id`
			LEFT JOIN `groups` ON `people`.`group`=`groups`.`id`
			WHERE `log`.`type`='skip' ]] .. cond .. [[ 
			GROUP BY `log`.`people_id`
			ORDER BY `groups`.`name`
		]])
		
		res = "ФИО; Класс; Кол-во пропусков\r\n"
		count = 0
		while sql.next() == 1 do
			if tonumber(sql.value(2)) >= ui.moreThanSpin.value then
				res = res .. sql.value(0) .. ";" .. sql.value(1) .. ";" .. sql.value(2) .. "\r\n";
				count = count + 1
			end
		end;
		qtt.write_text_file(ui.filenameLabel.text, res)
		ui.statusLabel:setText("Найдено " .. count .. " человек")
	end
);

ui:show()